package com.fm.parser.excel;

import com.fm.base.annotation.UnThreadSafe;
import com.fm.structure.base.Pair;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;

import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParserFactory;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/**
 * 抽象Excel2007读取器，excel2007的底层数据结构是xml文件，采用SAX的事件驱动的方法解析
 * xml，需要继承DefaultHandler，在遇到文件内容时，事件会触发，这种做法可以大大降低 内存的耗费，特别使用于大数据量的文件。
 *
 * @author finalmachine
 */
@UnThreadSafe
public class Excel2007Reader {
	private static class InnerHandle extends DefaultHandler {
		private enum XssfDataType {
            /**
             * excel单元格数据类型
             */
			BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER,
		}

		private static final Map<String, String> DATE_FORMAT = new HashMap<>();

		static {
            DATE_FORMAT.put("reserved-0x17", "");
            DATE_FORMAT.put("reserved-0x18", "");
            DATE_FORMAT.put("reserved-0x19", "");
            DATE_FORMAT.put("reserved-0x1a", "");
            DATE_FORMAT.put("reserved-0x1b", "");
            DATE_FORMAT.put("reserved-0x1c", "");
            DATE_FORMAT.put("reserved-0x1d", "");
            DATE_FORMAT.put("reserved-0x1e", "m/d/yy");
            DATE_FORMAT.put("reserved-0x1f", "m\"月\"d\"日\"yyyy\"年\"");
            DATE_FORMAT.put("reserved-0x20", "h\"时\"mm\"分\"");
            DATE_FORMAT.put("reserved-0x21", "h\"时\"mm\"分\"ss\"秒\"");
            DATE_FORMAT.put("reserved-0x22", "");
            DATE_FORMAT.put("reserved-0x23", "");
            DATE_FORMAT.put("reserved-0x24", "");
		}

		private StylesTable stylesTable;
		private ReadOnlySharedStringsTable sharedStringsTable;

		private XssfDataType dataType;
		private boolean vIsOpen;
		private StringBuffer value = new StringBuffer();
		private int curRow;
		private short formatIndex;
		private String formatString;
		private String columnName;

		private List<Pair<String, Object>> cells;
		private ExcelRowHandler rowHandler;

		private InnerHandle(StylesTable stylesTable, ReadOnlySharedStringsTable sharedStringsTable, ExcelRowHandler rowHandler) {
			this.stylesTable = stylesTable;
			this.sharedStringsTable = sharedStringsTable;
			this.rowHandler = rowHandler;
			this.cells = new LinkedList<>();
		}

		private void init() {
			curRow = 1;
		}

		@Override
		public void startElement(String uri, String localName, String name, Attributes attributes) {
			if ("inlineStr".equals(name) || "v".equals(name) || "t".equals(name)) {
				vIsOpen = true;
				// Clear contents cache
				value.setLength(0);
			} else if ("c".equals(name)) {
			    // c => cell
				dataType = XssfDataType.NUMBER;
				formatIndex = -1;
				formatString = null;

                columnName = attributes.getValue("r");

				String cellType = attributes.getValue("t");
				String cellStyleStr = attributes.getValue("s");

				if (cellType != null) {
					switch (cellType) {
						case "b":
							dataType = XssfDataType.BOOL;
							break;
						case "e":
							dataType = XssfDataType.ERROR;
							break;
						case "inlineStr":
							dataType = XssfDataType.INLINESTR;
							break;
						case "s":
							dataType = XssfDataType.SSTINDEX;
							break;
						case "str":
							dataType = XssfDataType.FORMULA;
							break;
						case "n":
							vIsOpen = true;
							// Clear contents cache
							value.setLength(0);
							break;
						default:
							// It's a number, but almost certainly one
							// with a special style or format
					}
				}
				if (cellStyleStr != null) {
					int styleIndex = Integer.parseInt(cellStyleStr);
					XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
					formatIndex = style.getDataFormat();
					formatString = style.getDataFormatString();
				}
			}
		}

		@Override
		public void endElement(String uri, String localName, String name) {
			Object thisCell;
			// v => contents of a cell
			if ("v".equals(name) || "t".equals(name)) {
				vIsOpen = false;
				switch (dataType) {
					case BOOL:
						char first = value.charAt(0);
						thisCell = first != '0';
						break;
					case ERROR:
						throw new ExcelCellError(value.toString(), columnName);
					case FORMULA:
						// A formula could result in a string value,
						// so always add double-quote characters.
						thisCell = value.toString();
						break;
					case INLINESTR:
						XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
						thisCell = rtsi.toString();
						break;
					case SSTINDEX:
						String sstIndex = value.toString();
						int idx = Integer.parseInt(sstIndex);
						XSSFRichTextString rtss = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx));
						thisCell = rtss.toString();
						break;
					case NUMBER:
						String n = value.toString();
						String localFormatString;
						if (formatString != null && DATE_FORMAT.containsKey(formatString.toLowerCase())) {
							localFormatString = DATE_FORMAT.get(formatString.toLowerCase());
						} else {
							localFormatString = formatString;
						}
						// 判断是否是日期格式
						if (DateUtil.isADateFormat(formatIndex, localFormatString)) {
							Double d = Double.parseDouble(n);
							thisCell = DateUtil.getJavaDate(d);
						} else if (formatString != null) {
							thisCell = new DataFormatter().formatRawCellContents(Double.parseDouble(n), formatIndex, localFormatString);
						} else {
							if (n.contains(".")) {
								thisCell = Double.parseDouble(n);
							} else {
								try {
									thisCell = Long.parseLong(n);
								} catch (NumberFormatException e) {
									thisCell = Double.parseDouble(n);
								}
							}
						}
						break;
					default:
						thisCell = "(TODO: Unexpected type: " + dataType + ")";
						break;
				}
                if (thisCell == null || (thisCell instanceof String && "".equals(thisCell))) {
                    cells.add(new Pair<>(columnName, null));
                } else {
                    cells.add(new Pair<>(columnName, thisCell));
                }
			} else if ("row".equals(name)) {
				rowHandler.doRow(curRow++, cells);
				cells = new LinkedList<>();
			}
		}

		@Override
		public void characters(char[] ch, int start, int length) {
			if (vIsOpen) {
				value.append(ch, start, length);
			}
		}
	}

	private interface SheetsProcessor {
		void doSheets(XSSFReader reader, InnerHandle handler, XMLReader sheetReader)
			throws IOException, ParserConfigurationException, OpenXML4JException, SAXException;
	}

	private static void prepare(StylesTable table) {
		table.putNumberFormat((short) 55, "上午/下午h\"时\"mm\"分\"");
		table.putNumberFormat((short) 56, "上午/下午h\"时\"mm\"分\"ss\"秒\"");
		table.putNumberFormat((short) 57, "yyyy\"年\"m\"月\"");
		table.putNumberFormat((short) 58, "m\"月\"d\"日\"");
	}

	/**
	 * 只遍历一个电子表格，其中sheetId为要遍历的sheet索引，从1开始
	 *
	 * @param filename full path filename
	 * @param sheetId  1 base
	 */
    public static void readOneSheet(String filename, int sheetId, ExcelRowHandler rowHandler) {
        try {
            readOneSheet(new FileInputStream(filename), sheetId, rowHandler);
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

	public static void readOneSheet(InputStream inputStream, int sheetId, ExcelRowHandler rowHandler) {
		read(inputStream, rowHandler, (reader, handler, sheetReader) -> {
			InputStream sheet2 = reader.getSheet("rId" + sheetId);
			handler.init();
			InputSource sheetSource = new InputSource(sheet2);
			sheetReader.parse(sheetSource);
			sheet2.close();
		});
	}

	public static void readOneSheet(final String filename, final String sheetName,  final ExcelRowHandler rowHandler) {
		try {
			readOneSheet(new FileInputStream(filename), sheetName, rowHandler);
		} catch (FileNotFoundException e) {
			throw new RuntimeException(e);
		}
	}

	public static void readOneSheet(InputStream inputStream, String sheetName,  ExcelRowHandler rowHandler) {
		read(inputStream, rowHandler, (reader, handler, sheetReader) -> {
			XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) reader.getSheetsData();
			while (sheets.hasNext()) {
				InputStream sheet = sheets.next();
				InputSource sheetSource = new InputSource(sheet);
				if (sheets.getSheetName().equals(sheetName)) {
					handler.init();
					sheetReader.parse(sheetSource);
				}
				sheet.close();
			}
		});
	}

	/**
	 * 遍历工作簿中所有的电子表格
	 *
	 * @param filename full path filename
	 */
	public static void read(String filename, ExcelRowHandler rowHandler) {
		try {
			read(new FileInputStream(filename), rowHandler);
		} catch (FileNotFoundException e) {
			throw new RuntimeException(e);
		}
	}

	public static void read(InputStream inputStream, ExcelRowHandler rowHandler) {
		read(inputStream, rowHandler, (reader, handler, sheetReader) -> {
			Iterator<InputStream> sheets = reader.getSheetsData();
			while (sheets.hasNext()) {
				handler.init();
				InputStream sheet = sheets.next();
				InputSource sheetSource = new InputSource(sheet);
				sheetReader.parse(sheetSource);
				sheet.close();
			}
		});
	}

	private static void read(InputStream inputStream, ExcelRowHandler rowHandler, SheetsProcessor processor) {
		try(OPCPackage pkg = OPCPackage.open(inputStream)) {
			XSSFReader reader = new XSSFReader(pkg);
			StylesTable table = reader.getStylesTable();
			prepare(table);
			InnerHandle handler = new InnerHandle(table, new ReadOnlySharedStringsTable(pkg), rowHandler);
			XMLReader sheetReader = SAXParserFactory.newInstance().newSAXParser().getXMLReader();
			sheetReader.setContentHandler(handler);
			processor.doSheets(reader, handler, sheetReader);
		} catch (IOException | ParserConfigurationException | OpenXML4JException | SAXException e) {
			throw new RuntimeException(e);
		}
	}
}